const express = require('express');
const mysql = require('mysql2');
const bodyParser = require('body-parser');
const cors = require('cors');
const jwt = require('jsonwebtoken');
const multer = require('multer');
const ExcelJS = require('exceljs');
const fs = require('fs');
const path = require('path');
const XLSX = require('xlsx');
// 禁用 dotenv 的输出
const originalLog = console.log;
console.log = function(...args) {
  if (args[0] && typeof args[0] === 'string' && args[0].includes('[dotenv@')) {
    return; // 跳过 dotenv 的输出
  }
  originalLog.apply(console, args);
};

require('dotenv').config({ 
  path: path.resolve(__dirname, '../.env'),
  silent: true
});

// 恢复 console.log
console.log = originalLog;

const app = express();

// 静态文件托管
app.use(express.static(path.join(__dirname)));

app.use(cors());
app.use(bodyParser.json());

const SECRET = process.env.JWT_SECRET || 'food_info_system_secret'; // JWT密钥

// 数据库连接
const db = mysql.createConnection({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '123456',
  database: process.env.DB_NAME || 'food_info_system',
  multipleStatements: true
});

db.connect(err => {
  if (err) {
    console.error('数据库连接失败:', err);
  } else {
    console.log('数据库连接成功');
  }
});

const upload = multer({ dest: 'uploads/' });

// 写入操作日志的工具函数
function addOperationLog(action, details, operator) {
  db.query('INSERT INTO operation_logs (action, details, operator) VALUES (?, ?, ?)', [action, details, operator], (err) => {
    if (err) console.error('写入操作日志失败:', err);
  });
}

// 用户注册
app.post('/api/register', (req, res) => {
  const { username, password, adminSecret, email } = req.body;
  if (!username || !password || !email) return res.status(400).json({ error: '用户名、密码和邮箱不能为空' });
  const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  if (!emailRegex.test(email)) return res.status(400).json({ error: '邮箱格式不正确' });
  const ADMIN_SECRET = process.env.ADMIN_SECRET || 'https123';
  let userRole = 'registered';
  if (adminSecret && adminSecret === ADMIN_SECRET) {
    userRole = 'admin';
  }
  const sql = 'INSERT INTO users (username, email, password, role) VALUES (?, ?, ?, ?)';
  db.query(sql, [username, email, password, userRole], (err, result) => {
    if (err) {
      return res.status(500).json({ error: '注册失败，用户名或邮箱可能已存在' });
    }
    addOperationLog('用户注册', `新用户 ${username} 注册成功`, username);
    res.json({ message: '注册成功' });
  });
});

// 用户登录
app.post('/api/login', (req, res) => {
  const { username, password } = req.body;
  if (!username || !password) return res.status(400).json({ error: '用户名和密码不能为空' });
  const sql = 'SELECT * FROM users WHERE username = ? AND password = ?';
  db.query(sql, [username, password], (err, results) => {
    if (err) {
      return res.status(500).json({ error: '登录失败' });
    }
    if (results.length === 0) return res.status(401).json({ error: '用户名或密码错误' });
    const user = results[0];
    db.query('UPDATE users SET last_login = NOW() WHERE id = ?', [user.id]);
    addOperationLog('用户登录', `用户 ${username} 登录系统`, username);
    const token = jwt.sign({ id: user.id, username: user.username, role: user.role }, SECRET, { expiresIn: '2h' });
    res.json({ id: user.id, username: user.username, role: user.role, token });
  });
});

// 鉴权中间件
function authMiddleware(req, res, next) {
  const token = req.headers['authorization'];
  if (!token) return res.status(401).json({ error: '未登录或token缺失' });
  jwt.verify(token, SECRET, (err, decoded) => {
    if (err) return res.status(401).json({ error: 'token无效或过期' });
    req.user = decoded;
    next();
  });
}

// 查询食物
app.get('/api/foods', (req, res) => {
  const { searchName, startDate, endDate, created_by } = req.query;
  let sql = 'SELECT * FROM foods WHERE 1=1';
  const params = [];
  
  if (searchName) {
    sql += ' AND name LIKE ?';
    params.push(`%${searchName}%`);
  }
  
  if (startDate) {
    sql += ' AND DATE(created_at) >= ?';
    params.push(startDate);
  }
  
  if (endDate) {
    sql += ' AND DATE(created_at) <= ?';
    params.push(endDate);
  }
  
  if (created_by) {
    sql += ' AND created_by = ?';
    params.push(created_by);
  }
  
  sql += ' ORDER BY created_at DESC';
  
  db.query(sql, params, (err, results) => {
    if (err) return res.status(500).json({ error: '查询失败' });
    res.json(results);
  });
});

// 添加食物（带图片上传）
app.post('/api/foods', authMiddleware, upload.single('photo'), (req, res) => {
  console.log('[添加食物] req.body:', req.body); // 调试用
  const { name, description, created_by, origin, producer, season, production_date } = req.body;
  const photo = req.file ? req.file.filename : null;
  if (!name) return res.status(400).json({ error: '名称不能为空' });
  const sql = 'INSERT INTO foods (name, description, created_by, photo, origin, producer, season, production_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?)';
  db.query(sql, [name, description, created_by, photo, origin, producer, season, production_date], (err, result) => {
    if (err) return res.status(500).json({ error: '添加失败' });
    res.json({ message: '添加成功' });
  });
});

// 添加食材（带图片上传）
app.post('/api/ingredients', authMiddleware, upload.single('photo'), (req, res) => {
  const { name, description, created_by, origin, producer, category, production_date, price, supplier } = req.body;
  const photo = req.file ? req.file.filename : null;
  if (!name) return res.status(400).json({ error: '名称不能为空' });
  const sql = 'INSERT INTO ingredients (name, description, created_by, photo, origin, producer, category, production_date, price, supplier) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
  db.query(sql, [name, description, created_by, photo, origin, producer, category, production_date, price, supplier], (err, result) => {
    if (err) return res.status(500).json({ error: '添加失败' });
    res.json({ message: '添加成功' });
  });
});

// 查询食材
app.get('/api/ingredients', (req, res) => {
  const { created_by } = req.query;
  let sql = 'SELECT * FROM ingredients WHERE 1=1';
  const params = [];
  
  if (created_by) {
    sql += ' AND created_by = ?';
    params.push(created_by);
  }
  
  sql += ' ORDER BY created_at DESC';
  
  db.query(sql, params, (err, results) => {
    if (err) return res.status(500).json({ error: '查询失败' });
    res.json(results);
  });
});

// 添加食品（带图片上传）
app.post('/api/products', authMiddleware, upload.single('photo'), (req, res) => {
  const {
    name, description, created_by, origin, producer,
    production_date, price, supplier,
    main_ingredient, method, effect, relation
  } = req.body;
  const photo = req.file ? req.file.filename : null;
  if (!name) return res.status(400).json({ error: '名称不能为空' });
  // 检查所有必填字段
  if (!photo || !origin || !producer || !production_date || !price || !supplier || !main_ingredient || !method || !effect) {
    return res.status(400).json({ error: '有必填字段为空' });
  }
  const sql = 'INSERT INTO products (name, description, created_by, photo, origin, producer, production_date, price, supplier, main_ingredient, method, effect, relation) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
  db.query(sql, [name, description, created_by, photo, origin, producer, production_date, price, supplier, main_ingredient, method, effect, relation], (err, result) => {
    if (err) return res.status(500).json({ error: '添加失败' });
    res.json({ message: '添加成功' });
  });
});

// 查询食品
app.get('/api/products', (req, res) => {
  const { created_by } = req.query;
  let sql = 'SELECT * FROM products WHERE 1=1';
  const params = [];
  
  if (created_by) {
    sql += ' AND created_by = ?';
    params.push(created_by);
  }
  
  sql += ' ORDER BY created_at DESC';
  
  db.query(sql, params, (err, results) => {
    if (err) return res.status(500).json({ error: '查询失败' });
    res.json(results);
  });
});

// 提交建议
app.post('/api/suggestions', authMiddleware, (req, res) => {
  const {
    content, user_id, title, type, priority, status, contact_info, submitter, submit_time, votes
  } = req.body;
  if (!content) return res.status(400).json({ error: '内容不能为空' });
  const sql = `INSERT INTO suggestions (content, user_id, title, type, priority, status, contact_info, submitter, submit_time, votes)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
  db.query(sql, [content, user_id, title, type, priority, status || 'pending', contact_info, submitter, submit_time, votes || 0], (err, result) => {
    if (err) {
      console.error('建议插入数据库失败:', err); // 新增详细错误输出
      return res.status(500).json({ error: '提交失败' });
    }
    console.log('建议插入成功，数据库返回：', result); // 新增
    res.json({ message: '提交成功' });
  });
});

// 查询建议（带 voted 字段）
app.get('/api/suggestions', authMiddleware, (req, res) => {
  const { submitter, user_id } = req.query;
  let sql = 'SELECT *, 0 AS voted FROM suggestions WHERE 1=1';
  const params = [];
  if (user_id) {
    sql += ' AND user_id = ?';
    params.push(user_id);
  } else if (submitter) {
    sql += ' AND submitter = ?';
    params.push(submitter);
  }
  sql += ' ORDER BY submit_time DESC, id DESC';
  db.query(sql, params, (err, results) => {
    if (err) return res.status(500).json({ error: '查询失败' });
    res.json(results);
  });
});

// 管理员处理建议（修改状态）
app.patch('/api/suggestions/:id', authMiddleware, (req, res) => {
  if (req.user.role !== 'admin') {
    return res.status(403).json({ error: '无权限' });
  }
  const { id } = req.params;
  const { status } = req.body;
  if (!['pending', 'reviewing', 'approved', 'rejected'].includes(status)) {
    return res.status(400).json({ error: '无效的状态' });
  }
  db.query('UPDATE suggestions SET status = ? WHERE id = ?', [status, id], (err, result) => {
    if (err) return res.status(500).json({ error: '更新失败' });
    res.json({ message: '状态更新成功' });
  });
});

// 用户列表（仅管理员）
app.get('/api/users', authMiddleware, (req, res) => {
  console.log('GET /api/users 被调用，当前用户：', req.user && req.user.username);
  if (req.user.role !== 'admin') {
    console.log('GET /api/users 拒绝访问：无权限');
    return res.status(403).json({ error: '无权限' });
  }
  db.query('SELECT id, username, email, role, created_at, last_login FROM users ORDER BY CASE WHEN role = "admin" THEN 0 ELSE 1 END, id DESC', (err, results) => {
    if (err) {
      console.error('GET /api/users 查询失败', err);
      return res.status(500).json({ error: '查询失败' });
    }
    console.log('GET /api/users 返回用户列表:', results);
    res.json(results);
  });
});

// 删除单个用户
app.delete('/api/users/:username', authMiddleware, (req, res) => {
  if (req.user.role !== 'admin') {
    res.setHeader('Content-Type', 'application/json');
    return res.status(403).json({ error: '无权限' });
  }
  const { username } = req.params;
  if (!username) {
    res.setHeader('Content-Type', 'application/json');
    return res.status(400).json({ error: '参数缺失' });
  }
  // 查询被删用户的角色，禁止删除任何管理员账号
  db.query('SELECT role FROM users WHERE username = ?', [username], (err, results) => {
    if (err || results.length === 0) {
      res.setHeader('Content-Type', 'application/json');
      return res.status(404).json({ error: '用户不存在' });
    }
    if (results[0].role === 'admin') {
      res.setHeader('Content-Type', 'application/json');
      return res.status(400).json({ error: '不能删除管理员账号！' });
    }
    db.query('DELETE FROM users WHERE username = ?', [username], (err2, result) => {
      if (err2) {
        res.setHeader('Content-Type', 'application/json');
        return res.status(500).json({ error: '删除失败' });
      }
      if (result.affectedRows === 0) {
        res.setHeader('Content-Type', 'application/json');
        return res.status(404).json({ error: '用户不存在' });
      }
      addOperationLog('删除用户', `管理员删除了用户 ${username}`, req.user.username);
      // 只返回对象格式，避免前端解析异常
      res.setHeader('Content-Type', 'application/json');
      return res.json({ message: '删除成功' });
    });
  });
});

// 权限变更（示例接口，需前端配合调用）
app.post('/api/users/role', authMiddleware, (req, res) => {
  if (req.user.role !== 'admin') {
    return res.status(403).json({ error: '无权限' });
  }
  const { username, newRole } = req.body;
  if (!username || !newRole) return res.status(400).json({ error: '参数缺失' });
  db.query('SELECT role FROM users WHERE username = ?', [username], (err, results) => {
    if (err || results.length === 0) return res.status(404).json({ error: '用户不存在' });
    const oldRole = results[0].role;
    if (oldRole === 'admin') {
      return res.status(400).json({ error: '管理员账号不能更改角色！' });
    }
    db.query('UPDATE users SET role = ? WHERE username = ?', [newRole, username], (err2) => {
      if (err2) return res.status(500).json({ error: '更新失败' });
      addOperationLog('权限更新', `用户 ${username} 角色从 ${oldRole} 更改为 ${newRole === 'admin' ? '管理员' : newRole === 'registered' ? '注册用户' : '普通用户'}`, req.user.username);
      res.json({ message: '权限更新成功' });
    });
  });
});

// 管理员更新用户邮箱和角色
app.post('/api/users/update', authMiddleware, (req, res) => {
  const username = req.user.username; // 只用token里的username
  const { email, role } = req.body;
  if (!username || !email || !role) return res.status(400).json({ error: '参数缺失' });
  db.query('SELECT role FROM users WHERE username = ?', [username], (err, results) => {
    if (err || results.length === 0) return res.status(404).json({ error: '用户不存在' });
    // 禁止更改admin账号的角色
    if (results[0].role === 'admin' && role !== 'admin') {
      return res.status(400).json({ error: '不能更改管理员账号的角色！' });
    }
    db.query('UPDATE users SET email = ?, role = ? WHERE username = ?', [email, role, username], (err2) => {
      if (err2) return res.status(500).json({ error: '更新失败' });
      addOperationLog('用户信息更新', `用户 ${username} 更新了邮箱和角色`, username);
      res.json({ message: '更新成功' });
    });
  });
});

// 查询操作日志（最新20条）
app.get('/api/operation-logs', authMiddleware, (req, res) => {
  if (req.user.role !== 'admin') {
    return res.status(403).json({ error: '无权限' });
  }
  db.query('SELECT action, details, operator, created_at FROM operation_logs ORDER BY created_at DESC LIMIT 20', (err, results) => {
    if (err) return res.status(500).json({ error: '查询失败' });
    res.json(results);
  });
});

// 导出单表为 Excel（中文表头，支持自定义文件名）
app.get('/api/backup/:table', authMiddleware, async (req, res) => {
  if (req.user.role !== 'admin' && req.user.role !== 'registered') {
    return res.status(403).json({ error: '无权限' });
  }
  const table = req.params.table;
  const allowedTables = ['foods', 'ingredients', 'products'];
  if (!allowedTables.includes(table)) {
    return res.status(400).json({ error: '不支持的表名' });
  }
  db.query(`SELECT * FROM ${table}`, async (err, results) => {
    if (err) return res.status(500).json({ error: '查询失败' });
    try {
      const workbook = new ExcelJS.Workbook();
      const worksheet = workbook.addWorksheet(table);
      // 中文表头映射
      const headerMap = {
        foods: {
          id: 'ID', name: '名称', description: '描述', created_by: '创建人', created_at: '创建时间', photo: '图片', origin: '产地', producer: '生产单位', season: '出产季节', production_date: '出产日期'
        },
        ingredients: {
          id: 'ID', name: '名称', description: '描述', created_by: '创建人', created_at: '创建时间', photo: '图片', origin: '产地', producer: '生产单位', category: '分类', production_date: '出产日期', price: '价格', supplier: '供货商'
        },
        products: {
          id: 'ID', name: '名称', description: '描述', created_by: '创建人', created_at: '创建时间', photo: '图片', origin: '产地', producer: '生产单位', production_date: '出产日期', price: '价格', supplier: '供货商'
        }
      };
      if (results.length > 0) {
        // 只导出界面显示字段
        const showFieldsMap = {
          foods: ['photo', 'name', 'origin', 'production_date', 'producer', 'season', 'description'],
          ingredients: ['photo', 'name', 'category', 'origin', 'production_date', 'producer', 'price', 'supplier', 'description'],
          products: ['photo', 'name', 'origin', 'production_date', 'producer', 'price', 'supplier', 'main_ingredient', 'description', 'method', 'effect', 'relation']
        };
        const fieldNameMap = headerMap[table];
        const showFields = showFieldsMap[table];
        const columns = showFields.map(key => ({ header: fieldNameMap[key] || key, key }));
        worksheet.columns = columns;
        const photoColIdx = columns.findIndex(col => col.key === 'photo');
        results.forEach((row, rowIndex) => {
          // 只保留showFields字段
          const rowData = {};
          showFields.forEach(f => { rowData[f] = row[f]; });
          if (photoColIdx !== -1) rowData.photo = '';
          const excelRow = worksheet.addRow(rowData);
          excelRow.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true };
          if (row.photo && photoColIdx !== -1) {
            let imgPath = path.join(__dirname, 'uploads', row.photo);
            let ext = path.extname(row.photo).replace('.', '').toLowerCase();
            let found = false;
            if (!ext) {
              const tryExts = ['jpg', 'jpeg', 'png', 'gif', 'bmp', 'webp'];
              for (const e of tryExts) {
                if (fs.existsSync(imgPath + '.' + e)) {
                  imgPath = imgPath + '.' + e;
                  ext = e;
                  found = true;
                  break;
                }
              }
            }
            if (!found && fs.existsSync(imgPath)) {
              ext = 'jpg';
              found = true;
            }
            if (found && ["jpg","jpeg","png","gif","bmp","webp"].includes(ext)) {
              const imageId = workbook.addImage({
                filename: imgPath,
                extension: ext
              });
              const imgWidth = 80;
              const imgHeight = 80;
              const excelColWidth = imgWidth / 7;
              const excelRowHeight = imgHeight * 0.75;
              worksheet.getColumn(photoColIdx + 1).width = excelColWidth;
              worksheet.getRow(rowIndex + 2).height = excelRowHeight;
              const colWidthPx = excelColWidth * 7;
              const rowHeightPx = excelRowHeight / 0.75;
              const colOffset = (colWidthPx - imgWidth) / 2 / 7;
              const rowOffset = (rowHeightPx - imgHeight) / 2 / 0.75;
              worksheet.addImage(imageId, {
                tl: { col: photoColIdx + colOffset, row: rowIndex + 1 + rowOffset },
                ext: { width: imgWidth, height: imgHeight },
                editAs: 'oneCell'
              });
            }
          }
        });
      }
      // 文件名处理
      let filename = req.query.filename || `${table}_backup.xlsx`;
      if (!filename.endsWith('.xlsx')) filename += '.xlsx';
      res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      res.setHeader('Content-Disposition', `attachment; filename*=UTF-8''${encodeURIComponent(filename)}`);
      await workbook.xlsx.write(res);
      res.end();
    } catch (e) {
      res.status(500).json({ error: '导出失败' });
    }
  });
});

// 批量删除 foods/ingredients/products
app.delete('/api/:table/batch-delete', authMiddleware, (req, res) => {
  if (req.user.role !== 'admin') {
    return res.status(403).json({ error: '无权限' });
  }
  const table = req.params.table;
  const allowedTables = ['foods', 'ingredients', 'products'];
  if (!allowedTables.includes(table)) {
    return res.status(400).json({ error: '不支持的表名' });
  }
  const { ids } = req.body;
  if (!Array.isArray(ids) || ids.length === 0) {
    return res.status(400).json({ error: '缺少要删除的id列表' });
  }
  const placeholders = ids.map(() => '?').join(',');
  db.query(`DELETE FROM ${table} WHERE id IN (${placeholders})`, ids, (err, result) => {
    if (err) return res.status(500).json({ error: '删除失败' });
    res.json({ message: '删除成功', affectedRows: result.affectedRows });
  });
});

// 批量导出 foods/ingredients/products 选中数据为Excel
app.post('/api/backup/:table/selected', authMiddleware, async (req, res) => {
  if (req.user.role !== 'admin') {
    return res.status(403).json({ error: '无权限' });
  }
  const table = req.params.table;
  const allowedTables = ['foods', 'ingredients', 'products'];
  if (!allowedTables.includes(table)) {
    return res.status(400).json({ error: '不支持的表名' });
  }
  const { ids } = req.body;
  if (!Array.isArray(ids) || ids.length === 0) {
    return res.status(400).json({ error: '缺少要导出的id列表' });
  }
  db.query(`SELECT * FROM ${table} WHERE id IN (${ids.map(() => '?').join(',')})`, ids, async (err, results) => {
    if (err) return res.status(500).json({ error: '查询失败' });
    try {
      const workbook = new ExcelJS.Workbook();
      const worksheet = workbook.addWorksheet(table);
      // 中文表头映射
      const headerMap = {
        foods: {
          id: 'ID', name: '名称', description: '描述', created_by: '创建人', created_at: '创建时间', photo: '图片', origin: '产地', producer: '生产单位', season: '出产季节', production_date: '出产日期'
        },
        ingredients: {
          id: 'ID', name: '名称', description: '描述', created_by: '创建人', created_at: '创建时间', photo: '图片', origin: '产地', producer: '生产单位', category: '分类', production_date: '出产日期', price: '价格', supplier: '供货商'
        },
        products: {
          id: 'ID', name: '名称', description: '描述', created_by: '创建人', created_at: '创建时间', photo: '图片', origin: '产地', producer: '生产单位', production_date: '出产日期', price: '价格', supplier: '供货商'
        }
      };
      if (results.length > 0) {
        const columns = Object.keys(results[0]).map(key => ({ header: headerMap[table][key] || key, key }));
        worksheet.columns = columns;
        const photoColIdx = columns.findIndex(col => col.key === 'photo');
        results.forEach((row, rowIndex) => {
          // 克隆一份数据，photo字段设为空
          const rowData = { ...row };
          if (photoColIdx !== -1) rowData.photo = '';
          const excelRow = worksheet.addRow(rowData);
          // 设置整行居中
          excelRow.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true };
          // 插入图片
          if (row.photo && photoColIdx !== -1) {
            let imgPath = path.join(__dirname, 'uploads', row.photo);
            let ext = path.extname(row.photo).replace('.', '').toLowerCase();
            let found = false;
            if (!ext) {
              // 自动补全扩展名
              const tryExts = ['jpg', 'jpeg', 'png', 'gif', 'bmp', 'webp'];
              for (const e of tryExts) {
                if (fs.existsSync(imgPath + '.' + e)) {
                  imgPath = imgPath + '.' + e;
                  ext = e;
                  found = true;
                  break;
                }
              }
            }
            if (!found && fs.existsSync(imgPath)) {
              // 兼容无扩展名图片，假定为jpg
              ext = 'jpg';
              found = true;
            }
            if (found && ["jpg","jpeg","png","gif","bmp","webp"].includes(ext)) {
              console.log('图片存在:', imgPath, '即将插入Excel');
              const imageId = workbook.addImage({
                filename: imgPath,
                extension: ext
              });
              // 自动适配图片和单元格大小
              const imgWidth = 80;
              const imgHeight = 80;
              const excelColWidth = imgWidth / 7;      // 约等于11.43
              const excelRowHeight = imgHeight * 0.75; // 约等于60
              worksheet.getColumn(photoColIdx + 1).width = excelColWidth;
              worksheet.getRow(rowIndex + 2).height = excelRowHeight;
              // 图片居中
              const colWidthPx = excelColWidth * 7;
              const rowHeightPx = excelRowHeight / 0.75;
              const colOffset = (colWidthPx - imgWidth) / 2 / 7;
              const rowOffset = (rowHeightPx - imgHeight) / 2 / 0.75;
              worksheet.addImage(imageId, {
                tl: { col: photoColIdx + colOffset, row: rowIndex + 1 + rowOffset },
                ext: { width: imgWidth, height: imgHeight },
                editAs: 'oneCell'
              });
            } else if (!fs.existsSync(imgPath)) {
              console.log('图片不存在:', imgPath);
            } else {
              console.log('图片格式不支持:', imgPath);
            }
          }
        });
      }
      let filename = req.query.filename || `${table}_selected.xlsx`;
      if (!filename.endsWith('.xlsx')) filename += '.xlsx';
      res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      res.setHeader('Content-Disposition', `attachment; filename*=UTF-8''${encodeURIComponent(filename)}`);
      await workbook.xlsx.write(res);
      res.end();
    } catch (e) {
      res.status(500).json({ error: '导出失败' });
    }
  });
});

// 静态托管图片
app.use('/uploads', express.static('uploads'));

// 创建自定义表结构
app.post('/api/custom-tables', authMiddleware, (req, res) => {
  const { name, description, fields } = req.body;
  if (!name || !fields) return res.status(400).json({ error: '表名和字段定义不能为空' });
  // 表名唯一性校验
  db.query('SELECT COUNT(*) as cnt FROM custom_tables WHERE name = ?', [name], (err, results) => {
    if (err) return res.status(500).json({ error: '数据库错误' });
    if (results[0].cnt > 0) return res.status(400).json({ error: '表名已存在' });
    // 字段校验（只要非空且不重复即可）
    const fieldNames = new Set();
    for (const f of fields) {
      if (!f.name) return res.status(400).json({ error: '字段名不能为空' });
      if (fieldNames.has(f.name)) return res.status(400).json({ error: '字段名重复' });
      fieldNames.add(f.name);
      if (!f.type) return res.status(400).json({ error: '字段类型不能为空' });
      if (f.type === 'select' && (!Array.isArray(f.options) || f.options.length === 0)) return res.status(400).json({ error: 'select类型字段必须有选项' });
    }
    const created_by = req.user.id;
    const sql = 'INSERT INTO custom_tables (name, description, fields, created_by) VALUES (?, ?, ?, ?)';
    db.query(sql, [name, description, JSON.stringify(fields), created_by], (err2, result) => {
      if (err2) return res.status(500).json({ error: '创建表失败，表名可能已存在' });
      res.json({ message: '创建成功', id: result.insertId });
    });
  });
});

// 获取所有自定义表结构
app.get('/api/custom-tables', authMiddleware, (req, res) => {
  db.query('SELECT id, name, description, fields, created_by, created_at FROM custom_tables', (err, results) => {
    if (err) return res.status(500).json({ error: '查询失败' });
    results.forEach(r => { if (typeof r.fields === 'string') r.fields = JSON.parse(r.fields); });
    res.json(results);
  });
});

// 获取单个自定义表结构
app.get('/api/custom-tables/:id', authMiddleware, (req, res) => {
  db.query('SELECT id, name, description, fields, created_by, created_at FROM custom_tables WHERE id = ?', [req.params.id], (err, results) => {
    if (err || results.length === 0) return res.status(404).json({ error: '未找到表结构' });
    const table = results[0];
    if (typeof table.fields === 'string') table.fields = JSON.parse(table.fields);
    res.json(table);
  });
});

// 新增自定义表数据
app.post('/api/custom-tables/:id/data', authMiddleware, (req, res) => {
  const { data } = req.body;
  if (!data) return res.status(400).json({ error: '数据不能为空' });
  const created_by = req.user.id;
  db.query('INSERT INTO custom_table_data (table_id, data, created_by) VALUES (?, ?, ?)', [req.params.id, JSON.stringify(data), created_by], (err, result) => {
    if (err) return res.status(500).json({ error: '数据写入失败' });
    res.json({ message: '写入成功', id: result.insertId });
  });
});

// 查询自定义表数据
app.get('/api/custom-tables/:id/data', authMiddleware, (req, res) => {
  db.query('SELECT id, data, created_by, created_at FROM custom_table_data WHERE table_id = ?', [req.params.id], (err, results) => {
    if (err) return res.status(500).json({ error: '查询失败' });
    results.forEach(r => { if (typeof r.data === 'string') r.data = JSON.parse(r.data); });
    res.json(results);
  });
});

// 删除自定义表结构及其数据
app.delete('/api/custom-tables/:id', authMiddleware, (req, res) => {
  if (req.user.role !== 'admin') {
    return res.status(403).json({ error: '无权限' });
  }
  // 先删数据，再删表结构，任何一步出错都输出日志
  db.query('DELETE FROM custom_table_data WHERE table_id = ?', [req.params.id], (err) => {
    if (err) {
      console.error('删除custom_table_data失败:', err);
      // 不直接return，继续尝试删除表结构
    }
    db.query('DELETE FROM custom_tables WHERE id = ?', [req.params.id], (err2, result) => {
      if (err2) {
        console.error('删除custom_tables失败:', err2);
        return res.status(500).json({ error: '删除表结构失败' });
      }
      if (result.affectedRows === 0) {
        return res.status(404).json({ error: '表格不存在或已被删除' });
      }
      res.json({ message: '删除成功' });
    });
  });
});

// 工具函数：Excel列宽自适应，中文算2，英文算1
function getExcelWidth(str) {
  if (!str) return 0;
  return String(str).split('').reduce((w, ch) => w + (ch.charCodeAt(0) > 255 ? 2 : 1), 0);
}

// 导出自定义表格为Excel
app.get('/api/custom-tables/:id/export', authMiddleware, async (req, res) => {
  const tableId = req.params.id;
  const filename = req.query.filename || 'custom_table.xlsx';
  db.query('SELECT name, fields FROM custom_tables WHERE id = ?', [tableId], (err, tables) => {
    if (err || tables.length === 0) return res.status(404).json({ error: '未找到表结构' });
    const table = tables[0];
    let fields = table.fields;
    if (typeof fields === 'string') fields = JSON.parse(fields);
    db.query('SELECT data FROM custom_table_data WHERE table_id = ?', [tableId], async (err2, rows) => {
      if (err2) return res.status(500).json({ error: '查询数据失败' });
      try {
        const workbook = new ExcelJS.Workbook();
        const worksheet = workbook.addWorksheet(table.name || '自定义表');
        worksheet.columns = fields.map(f => ({ header: f.label || f.name, key: f.name }));
        rows.forEach(row => {
          let rowData = row.data;
          if (typeof rowData === 'string') rowData = JSON.parse(rowData);
          worksheet.addRow(rowData);
        });
        // 设置所有单元格内容居中
        worksheet.eachRow((row, rowNumber) => {
          row.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true };
        });
        // 列宽自适应内容（中文算2，英文算1，最大60）
        worksheet.columns.forEach(col => {
          let maxLength = col.header ? getExcelWidth(col.header) : 10;
          col.eachCell({ includeEmpty: true }, cell => {
            const len = getExcelWidth(cell.value);
            if (len > maxLength) maxLength = len;
          });
          col.width = Math.max(10, Math.min(60, maxLength + 2));
        });
        let outFilename = filename.endsWith('.xlsx') ? filename : filename + '.xlsx';
        res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        res.setHeader('Content-Disposition', `attachment; filename*=UTF-8''${encodeURIComponent(outFilename)}`);
        await workbook.xlsx.write(res);
        res.end();
      } catch (e) {
        res.status(500).json({ error: '导出失败' });
      }
    });
  });
});

// 游客访问统计：增加一次
app.post('/api/guest-visit', (req, res) => {
  db.query('UPDATE guest_visits SET count = count + 1 WHERE id = 1', (err) => {
    if (err) return res.status(500).json({ error: '统计失败' });
    res.json({ message: 'ok' });
  });
});
// 普通用户数量统计：获取总次数
app.get('/api/guest-visit', (req, res) => {
  db.query('SELECT count FROM guest_visits WHERE id = 1', (err, results) => {
    if (err) return res.status(500).json({ error: '获取失败' });
    res.json({ normalUserCount: results[0]?.count || 0 });
  });
});

// 游客登录：插入gusers并返回当前guest总数和新id、总用户数
app.post('/api/guest-user', (req, res) => {
  db.query('INSERT INTO gusers (role) VALUES ("guest")', (err, result) => {
    if (err) return res.status(500).json({ error: '游客登录失败' });
    db.query('SELECT COUNT(*) AS count FROM gusers WHERE role="guest"', (err2, results) => {
      if (err2) return res.status(500).json({ error: '统计失败' });
      db.query('SELECT COUNT(*) AS userCount FROM users', (err3, results2) => {
        if (err3) return res.status(500).json({ error: '总用户统计失败' });
        res.json({
          guestCount: results[0].count,
          guestId: result.insertId,
          totalUserCount: results2[0].userCount + results[0].count
        });
      });
    });
  });
});
// 获取普通用户数量（gusers表）
app.get('/api/guest-user', (req, res) => {
  db.query('SELECT COUNT(*) AS count FROM gusers WHERE role="guest"', (err, results) => {
    if (err) return res.status(500).json({ error: '获取失败' });
    res.json({ normalUserCount: results[0].count });
  });
});
// 获取总用户数（users+gusers）
app.get('/api/total-user', (req, res) => {
  db.query('SELECT COUNT(*) AS userCount FROM users', (err, results) => {
    if (err) return res.status(500).json({ error: '获取失败' });
    db.query('SELECT COUNT(*) AS guestCount FROM gusers WHERE role="guest"', (err2, results2) => {
      if (err2) return res.status(500).json({ error: '获取失败' });
      res.json({ totalUserCount: results[0].userCount + results2[0].guestCount });
    });
  });
});

// 更新 foods 表单条数据
app.put('/api/foods/:id', authMiddleware, (req, res) => {
  const id = req.params.id;
  const updateData = req.body;
  const fields = Object.keys(updateData).filter(f => f !== 'id');
  if (fields.length === 0) return res.status(400).json({ error: '无可更新字段' });
  const sql = `UPDATE foods SET ${fields.map(f => `${f}=?`).join(', ')} WHERE id=?`;
  const values = fields.map(f => updateData[f]);
  values.push(id);
  db.query(sql, values, (err, result) => {
    if (err) return res.status(500).json({ error: '更新失败' });
    if (result.affectedRows === 0) return res.status(404).json({ error: '未找到数据' });
    res.json({ message: '更新成功' });
  });
});
// 更新 ingredients 表单条数据
app.put('/api/ingredients/:id', authMiddleware, (req, res) => {
  const id = req.params.id;
  const updateData = req.body;
  const fields = Object.keys(updateData).filter(f => f !== 'id');
  if (fields.length === 0) return res.status(400).json({ error: '无可更新字段' });
  const sql = `UPDATE ingredients SET ${fields.map(f => `${f}=?`).join(', ')} WHERE id=?`;
  const values = fields.map(f => updateData[f]);
  values.push(id);
  db.query(sql, values, (err, result) => {
    if (err) return res.status(500).json({ error: '更新失败' });
    if (result.affectedRows === 0) return res.status(404).json({ error: '未找到数据' });
    res.json({ message: '更新成功' });
  });
});
// 更新 products 表单条数据
app.put('/api/products/:id', authMiddleware, (req, res) => {
  const id = req.params.id;
  const updateData = req.body;
  const fields = Object.keys(updateData).filter(f => f !== 'id');
  if (fields.length === 0) return res.status(400).json({ error: '无可更新字段' });
  const sql = `UPDATE products SET ${fields.map(f => `${f}=?`).join(', ')} WHERE id=?`;
  const values = fields.map(f => updateData[f]);
  values.push(id);
  db.query(sql, values, (err, result) => {
    if (err) return res.status(500).json({ error: '更新失败' });
    if (result.affectedRows === 0) return res.status(404).json({ error: '未找到数据' });
    res.json({ message: '更新成功' });
  });
});

// 建议投票接口（防止重复投票）
app.post('/api/suggestions/:id/vote', authMiddleware, (req, res) => {
  const { id } = req.params;
  const userId = req.user.id;
  // 检查是否已投票
  db.query('SELECT 1 FROM suggestion_votes WHERE user_id = ? AND suggestion_id = ?', [userId, id], (err, results) => {
    if (err) return res.status(500).json({ error: '投票失败' });
    if (results.length > 0) return res.status(400).json({ error: '您已投过票' });
    // 未投票则写入记录并加1
    db.query('INSERT INTO suggestion_votes (user_id, suggestion_id) VALUES (?, ?)', [userId, id], (err2) => {
      if (err2) return res.status(500).json({ error: '投票失败' });
      db.query('UPDATE suggestions SET votes = votes + 1 WHERE id = ?', [id], (err3) => {
        if (err3) return res.status(500).json({ error: '投票失败' });
        res.json({ message: '投票成功' });
      });
    });
  });
});

// 数据导入接口
app.post('/api/import', authMiddleware, upload.single('file'), async (req, res) => {
  const table = req.body.table;
  const file = req.file;
  if (!table || !file) return res.status(400).json({ error: '参数缺失' });
  // 表字段定义，photo字段加上
  const tableFields = {
    foods: ['name', 'description', 'origin', 'producer', 'season', 'production_date', 'photo'],
    ingredients: ['name', 'description', 'origin', 'producer', 'category', 'production_date', 'price', 'supplier', 'photo'],
    products: ['name', 'description', 'origin', 'producer', 'production_date', 'price', 'supplier', 'main_ingredient', 'method', 'effect', 'relation', 'photo']
  };
  // 中文表头映射
  const headerMapAll = {
    foods: {
      '图片': 'photo', '名称': 'name', '产地': 'origin', '出产日期': 'production_date', '生产单位': 'producer', '出产季节': 'season', '描述': 'description'
    },
    ingredients: {
      '图片': 'photo', '名称': 'name', '分类': 'category', '产地': 'origin', '出产日期': 'production_date', '生产单位': 'producer', '价格': 'price', '供货商': 'supplier', '描述': 'description'
    },
    products: {
      '图片': 'photo', '名称': 'name', '产地': 'origin', '出产日期': 'production_date', '生产单位': 'producer', '价格': 'price', '供货商': 'supplier', '主要原料': 'main_ingredient', '描述': 'description', '工艺': 'method', '功效': 'effect', '关联': 'relation'
    }
  };
  if (!tableFields[table]) return res.status(400).json({ error: '不支持的表类型' });
  try {
    const ExcelJS = require('exceljs');
    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.readFile(file.path);
    const worksheet = workbook.worksheets[0];
    // 提取图片并建立行号与图片文件名的映射
    const imageIdMap = {};
    worksheet.getImages().forEach(img => {
      const image = workbook.getImage(img.imageId);
      const ext = image.extension || 'png';
      const filename = `${Date.now()}_${Math.random().toString(36).slice(2,8)}.${ext}`;
      const filepath = path.join(__dirname, 'uploads', filename);
      require('fs').writeFileSync(filepath, image.buffer);
      imageIdMap[img.range.tl.nativeRow + 1] = filename;
    });
    // 读取表头，支持中文自动映射
    const firstRow = worksheet.getRow(1);
    const colCount = firstRow.cellCount;
    const headerMap = headerMapAll[table];
    const colHeaders = [];
    for (let i = 1; i <= colCount; i++) {
      let header = firstRow.getCell(i).value;
      if (headerMap[header]) header = headerMap[header];
      colHeaders.push(header);
    }
    // 读取数据行
    const fields = tableFields[table];
    const json = [];
    worksheet.eachRow((row, rowNumber) => {
      if (rowNumber === 1) return; // 跳过表头
      const rowData = {};
      for (let i = 1; i <= colCount; i++) {
        rowData[colHeaders[i - 1]] = row.getCell(i).value;
      }
      // 如果该行有图片，photo字段用图片文件名覆盖
      if (imageIdMap[rowNumber]) {
        rowData['photo'] = imageIdMap[rowNumber];
      }
      json.push(rowData);
    });
    // 字段校验
    const invalid = json.some(row => fields.some(f => !(f in row)));
    if (invalid) {
      require('fs').unlinkSync(file.path);
      return res.status(400).json({ error: '数据字段不符合要求' });
    }
    // 自动加created_by字段
    const userId = req.user.id;
    const fieldsWithCreatedBy = [...fields, 'created_by'];
    const values = json.map(row => [...fields.map(f => row[f]), userId]);
    const placeholders = '(' + fieldsWithCreatedBy.map(() => '?').join(',') + ')';
    const sql = `INSERT INTO ${table} (${fieldsWithCreatedBy.join(',')}) VALUES ${values.map(() => placeholders).join(',')}`;
    const flatValues = [].concat(...values);
    require('fs').unlinkSync(file.path);
    db.query(sql, flatValues, (err, result) => {
      if (err) return res.status(500).json({ error: '导入失败' });
      res.json({ message: '导入成功', count: result.affectedRows });
    });
  } catch (e) {
    if (file && file.path) require('fs').unlinkSync(file.path);
    res.status(500).json({ error: '文件解析失败' });
  }
});

const PORT = 3000;
app.listen(PORT, () => {
  console.log(`服务器已启动，端口：${PORT}`);
}); 